/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import model.Driver;
import model.Route;
import model.Shipment;
import model.Truck;

/**
 *
 * @author joseph
 */
public class ShipmentHandler {

    private DatabaseConnection db;

    public ShipmentHandler(DatabaseConnection db) {
        this.db = db;
    }

    public ArrayList<String[]> getShipmentOverview() throws SQLException {
        ArrayList<String[]> list = new ArrayList<>();
        String sql = "SELECT\n"
                + "	(SELECT f_name FROM driver WHERE driver_id = id) AS First_Name,\n"
                + "	(SELECT l_name FROM driver WHERE driver_id = id) AS Last_Name,\n"
                + "	(SELECT c_name FROM city WHERE origin_id = id) AS StartCity,\n"
                + "	(SELECT c_name FROM city WHERE destination_id = id) AS Destination,\n"
                + "	(SELECT distance FROM route WHERE origin_id = origin AND destination_id = destination) AS distance,\n"
                + "	(SELECT g_name FROM goods_type WHERE Goods_type = id) AS Goods_Type,\n"
                + "	(SELECT ROUND(\n"
                + "				IF(dangerousgoods, price_per_km*distance*1.1, price_per_km*distance),0\n"
                + "			) \n"
                + "			FROM goods_price \n"
                + "			WHERE goods_type = shipment.goods_type \n"
                + "			AND (amount <= maximum AND weight <= maxWeight) ORDER BY maximum ASC, maxWeight ASC LIMIT 1) AS price,\n"
                + "	DAYNAME(Shipment_date) AS DayName,\n"
                + "	DATE_FORMAT(shipment_date,GET_FORMAT(DATE,'EUR')) AS Shipment_date,\n"
                + "	WEEK(Shipment_date)+1 AS Week\n"
                + "	FROM shipment;";
        ResultSet rs = db.selectQuery(sql);
        while (rs.next()) {
            String[] str = {rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), 
                rs.getString(6), rs.getString(7), DateUtil.getDayNameFromString(rs.getString(8)), rs.getString(9), rs.getString(10)};
            list.add(str);
        }
        return list;
    }

    public void addShipment(int amount, String date, boolean dangerousGoods, int goodsType, int weight, 
            String comment, String customer, String startAddress, String endAddress, Truck truck, 
            Driver driver, Route route) throws SQLException {
        
        Shipment s = new Shipment(amount, date, dangerousGoods, goodsType, weight, comment, 
                customer, startAddress, endAddress, truck, driver, route);
        
        String sql = "insert into shipment "
                + "(shipment_date, dangerousgoods, goods_type, weight, amount, "
                + "description, customer, address1, address2, origin_id, destination_id, truck_id, driver_id) "
                + "values ("
                + "'" + s.getDate() + "',"
                + s.isDangerousGoods() + ","
                + s.getGoodsType() + ","
                + s.getWeight() + ","
                + s.getAmount() + ",'"
                + s.getComment() + "','"
                + s.getCustomer() + "','"
                + s.getStartAddress() + "','"
                + s.getEndAddress() + "',"
                + s.getPickUpCityID() + ","
                + s.getDeliveryCityID() + ","
                + s.getTruck().getId() + ","
                + s.getDriver().getId()
                + ")";
        db.query(sql);
    }
}
